﻿using System.Diagnostics;
using System.IO;
using Microsoft.Office.Interop.Excel;

namespace HmLab.Func2;

internal class LbExcelCopyRange {
  public class Copier(LbExcelCopyRange _impl) {
    public void Copy(Workbook toBook, BookFrom[] from) => _impl.CopyImpl(toBook, from);
  }

  public void Run() {
    System.Console.WriteLine("----LbExcelCopyRange");

    string toPath = @"D:\work\personal\nina\2023haimen2\workshop_cslab\输Q出f2\月合并报表.xlsx";
    string fromPath = @"D:\work\personal\nina\2023haimen2\workshop_cslab\输O入0206f2\店铺分表\粤垦店-2023年12期月报.xls";

    var MakeBook = (string fromBk, string? fromSh, string toSh, string range) => {
      CellCopy cellCfg = new(range, "A1");
      SheetTo shCfg = new(toSh, [new SheetFrom(fromSh, [cellCfg])]);
      return new BookFrom(fromBk, [shCfg]);
    };

    BookFrom[] fromBookCfg = [
      MakeBook(fromPath, null, "粤垦", "A1:AY80"),
    ];

    Application excelApp = new() {
      Visible = true,
      DisplayAlerts = false,
    };

    Workbooks books = excelApp.Workbooks;
    var OpenBook = (string path) => {
      Debug.Assert(File.Exists(path));
      return books.Open(path, UpdateLinks: false);
    };

    Workbook toBook = OpenBook(toPath);
    foreach (BookFrom bookCfg in fromBookCfg) {
      Workbook fromBook = OpenBook(bookCfg.Path);

      foreach (SheetTo cfg in bookCfg.Sheets) {
        Worksheet toSheet = toBook.Sheets[cfg.Name];
        CopySheetTo(toSheet, fromBook, cfg.From);
        //return;
      }

      fromBook.Activate();
      excelApp.CutCopyMode = 0;
      fromBook.Close();
    }
  }

  /// <summary>
  /// <see cref="Haimen.Func2.ExcelRangeCopier"/>
  /// </summary>
  void CopyImpl(Workbook toBook, BookFrom[] from) {
    Application excelApp = toBook.Application;
    Workbooks books = excelApp.Workbooks;

    foreach (BookFrom bookCfg in from) {
      Workbook fromBook = books.Open(bookCfg.Path, UpdateLinks: false);

      foreach (SheetTo cfg in bookCfg.Sheets) {
        Worksheet toSheet = toBook.Sheets[cfg.Name];
        CopySheetTo(toSheet, fromBook, cfg.From);
      }

      fromBook.Activate();
      excelApp.CutCopyMode = 0;
      fromBook.Close();
    }
  }

  void CopySheetTo(Worksheet toSheet, Workbook fromBook, SheetFrom[] fromCfg) {
    foreach (SheetFrom sheetCfg in fromCfg) {
      Worksheet fromSheet = GetSheet(fromBook, sheetCfg);

      foreach (CellCopy cell in sheetCfg.Cells) {
        fromSheet.Activate();
        Range fromRange = GetRange(fromSheet, cell.FromRange);
        fromRange.Select();
        fromRange.Copy();

        toSheet.Activate();
        Range toCell = toSheet.Range[cell.ToCell];
        toCell.Select();

        toSheet.Paste();
        //toCell.PasteSpecial(XlPasteType.xlPasteFormats);
        //toCell.PasteSpecial(XlPasteType.xlPasteColumnWidths);
        toCell.PasteSpecial(XlPasteType.xlPasteValuesAndNumberFormats); // 粘贴为值
      }
    }
  }

  Worksheet GetSheet(Workbook book, SheetFrom sheetCfg) {
    if (sheetCfg.Name == null) {
      return book.Sheets[1];
    }
    return book.Sheets[sheetCfg.Name];
  }

  Range GetRange(Worksheet sheet, string rangeCfg) {
    int splitIndex = rangeCfg.IndexOf(':');
    if (splitIndex < 0) { // 没冒号，表示只有一个单元格
      return sheet.Range[rangeCfg];
    }
    if (splitIndex == 0) { // 起始没填，补上第一个单元格
      return sheet.Range["A1" + rangeCfg];
    }
    if (splitIndex != rangeCfg.Length - 1) { // 前后都有填，原样使用
      return sheet.Range[rangeCfg];
    }
    // 结束没填，找最后一个单元格
    string start = rangeCfg[..splitIndex];
    Range lastCell = sheet.Cells.SpecialCells(
      XlCellType.xlCellTypeLastCell, System.Type.Missing);
    return sheet.Range[sheet.Range[start], lastCell];
  }
}

internal record CellCopy(string FromRange, string ToCell);
internal record SheetFrom(string? Name, CellCopy[] Cells);//int Index,
internal record SheetTo(string Name, SheetFrom[] From);
internal record BookFrom(string Path, SheetTo[] Sheets);
